﻿/*
=============================================================================
Author:魏宝辉通过CodeSmithe生成
CreatedTime:2012-9-9
Description:master三层结构数据访问层
=============================================================================
*/
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SQLite;
using FTchina.Models;//引入模型层
namespace FTchina.DAL
{
	public static partial class MasterService
	{
		#region 增加一条数据
		public static int AddMaster(Master master)
		{
			string sql =
			"INSERT INTO master(Master_Name,Master_Password,link,LasTime,LastIP,Cookiess,State,power,pname,nicheng) "+
			"VALUES (@Master_Name,@Master_Password,@link,@LasTime,@LastIP,@Cookiess,@State,@power,@pname,@nicheng)";
			
			 
				SQLiteParameter[] para = new SQLiteParameter[]
				{
						
					new SQLiteParameter("@Master_Name",master.Master_Name),
						
					new SQLiteParameter("@Master_Password",master.Master_Password),
						
					new SQLiteParameter("@link",master.Link),
						
					new SQLiteParameter("@LasTime",master.LasTime),
						
					new SQLiteParameter("@LastIP",master.LastIP),
						
					new SQLiteParameter("@Cookiess",master.Cookiess),
						
					new SQLiteParameter("@State",master.State),
						
					new SQLiteParameter("@power",master.Power),
						
					new SQLiteParameter("@pname",master.Pname),
						
					new SQLiteParameter("@nicheng",master.Nicheng)
				};
				int newId = DBHelper.ExecuteCommand(sql, para);
				return newId;
			 
		}
		#endregion
		#region 删除数据以主键，标识列，唯一列构建的删除
			
		public static int DeleteMasterById(int id)	
		{
			string sql="DELETE From  master  Where id=@id";
			 SQLiteParameter[] para = new SQLiteParameter[]
				{
					new SQLiteParameter("@id", id)
				};
				return DBHelper.ExecuteCommand(sql, para);
			 
		}		
		
		#endregion
		#region 修改数据
		//考虑标识列与主键不一致情形
		//以标识列修改实体
		public static int ModifyMasterByid(Master master)
		{
		string sql ="UPDATE "+"master"+" set "+
					"Master_Name=@Master_Name, "+
					"Master_Password=@Master_Password, "+
					"link=@link, "+
					"LasTime=@LasTime, "+
					"LastIP=@LastIP, "+
					"Cookiess=@Cookiess, "+
					"State=@State, "+
					"power=@power, "+
					"pname=@pname, "+
					"nicheng=@nicheng "+ 
				" Where id=@id";
			 
				SQLiteParameter[] para = new SQLiteParameter[]
				{
					new SQLiteParameter("@id",master.Id),
					new SQLiteParameter("@Master_Name",master.Master_Name),
					new SQLiteParameter("@Master_Password",master.Master_Password),
					new SQLiteParameter("@link",master.Link),
					new SQLiteParameter("@LasTime",master.LasTime),
					new SQLiteParameter("@LastIP",master.LastIP),
					new SQLiteParameter("@Cookiess",master.Cookiess),
					new SQLiteParameter("@State",master.State),
					new SQLiteParameter("@power",master.Power),
					new SQLiteParameter("@pname",master.Pname),
					new SQLiteParameter("@nicheng",master.Nicheng)
				};
				return DBHelper.ExecuteCommand(sql, para);
			 
		}					
		#endregion
		#region 查询数据
		public static IList<Master> GetAllMasters()
		{
			string sqlAll = "SELECT * FROM master";
			return GetMastersBySql(sqlAll);
		}
		//以标识列获得对象
		public static Master GetMasterByid(object id)
		{
			string sql = "SELECT * FROM "+"master"+" WHERE id = @id";
			
            using (SQLiteDataReader reader = DBHelper.GetReader(sql, new SQLiteParameter("@id", id)))
			{
				 
				if (reader.Read())
				{
					Master master = new Master();
					master.Id=Convert.ToInt32(reader["id"]);
					master.Master_Name=Convert.ToString(reader["Master_Name"]);
					master.Master_Password=Convert.ToString(reader["Master_Password"]);
					master.Link=Convert.ToString(reader["link"]);
					master.LasTime=Convert.ToDateTime(reader["LasTime"]);
					master.LastIP=Convert.ToString(reader["LastIP"]);
					master.Cookiess=Convert.ToString(reader["Cookiess"]);
					master.State=Convert.ToString(reader["State"]);
					master.Power=Convert.ToString(reader["power"]);
					master.Pname=Convert.ToString(reader["pname"]);
					master.Nicheng=Convert.ToString(reader["nicheng"]);
					reader.Close();
					
					return master;
					}
				else
				{
					reader.Close();
					return null;
				}
			}
			 
		}
					
		//私有方法供构建好的SQL语句调用
		private static IList<Master> GetMastersBySql(string safeSql)
		{
			List<Master> list = new List<Master>();
			 
            using (DataTable table = DBHelper.GetDataTable(safeSql))
			{
				foreach (DataRow row in table.Rows)
				{
					Master master = new Master();
					master.Id=Convert.ToInt32(row["id"]);
					master.Master_Name=Convert.ToString(row["Master_Name"]);
					master.Master_Password=Convert.ToString(row["Master_Password"]);
					master.Link=Convert.ToString(row["link"]);
					master.LasTime=Convert.ToDateTime(row["LasTime"]);
					master.LastIP=Convert.ToString(row["LastIP"]);
					master.Cookiess=Convert.ToString(row["Cookiess"]);
					master.State=Convert.ToString(row["State"]);
					master.Power=Convert.ToString(row["power"]);
					master.Pname=Convert.ToString(row["pname"]);
					master.Nicheng=Convert.ToString(row["nicheng"]);
					list.Add(master);
				}
				return list;
			}
             
		}
		private static IList<Master> GetMastersBySql( string sql, params SQLiteParameter[] values )
		{
			List<Master> list = new List<Master>();
			 
            using (DataTable table = DBHelper.GetDataTable(sql, values))
			{
				foreach (DataRow row in table.Rows)
				{
					Master master = new Master();
					master.Id=Convert.ToInt32(row["id"]);
					master.Master_Name=Convert.ToString(row["Master_Name"]);
					master.Master_Password=Convert.ToString(row["Master_Password"]);
					master.Link=Convert.ToString(row["link"]);
					master.LasTime=Convert.ToDateTime(row["LasTime"]);
					master.LastIP=Convert.ToString(row["LastIP"]);
					master.Cookiess=Convert.ToString(row["Cookiess"]);
					master.State=Convert.ToString(row["State"]);
					master.Power=Convert.ToString(row["power"]);
					master.Pname=Convert.ToString(row["pname"]);
					master.Nicheng=Convert.ToString(row["nicheng"]);
					list.Add(master);
				}
				return list;
			}
            
			 
		}
		#endregion
		 
	}
}

